package com.hm.health.provider;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.goodix.http.jsonparams.CfgDetailInfo;
import com.goodix.http.jsonparams.DeviceInfo;
import com.goodix.http.jsonparams.EarHangInfo;

/**
 * Created by liwenlin on 16-12-29.
 */

public class DbProvider {
    private static final String TAG = "DbProvider";

    private static final String DATABASE_NAME = "hmHbd.db";
    private static final int DATABASE_VERSION = 2;

    public static final String SCENEINFO_TABLE = "SceneInfo";
    public static final String SCENEINFO_SCENE_ID_COLUMN = "sceneid";
    public static final String SCENEINFO_SCENE_NAME_COLUMN = "scenename";

    public static final String SUBJECTINFO_TABLE = "SubjectInfo";
    public static final String SUBJECTINFO_SCENE_ID_COLUMN = "subjectid";
    public static final String SUBJECTINFO_SCENE_NAME_COLUMN = "subjectname";

    public static final String EARHANGINFO_TABLE = "EarHangInfo";
    public static final String EARHANGINFO_SCENE_ID_COLUMN = "earhangid";
    public static final String EARHANGINFO_SCENE_NAME_COLUMN = "earhangname";

    public static final String SKININFO_TABLE = "SkinInfo";
    public static final String SKININFO_SCENE_ID_COLUMN = "skinid";
    public static final String SKININFO_SCENE_NAME_COLUMN = "skinname";

    public static final String DEVICENFO_TABLE = "DeviceInfo";
    public static final String DEVICENFO_OWNER = "deviceOwner";
    public static final String DEVICENFO_DEVICE_NAME = "deviceName";

    // Variable to hold the database instance
    protected SQLiteDatabase mDb;
    // Context of the application using the database.
    private final Context mContext;
    // Database open/upgrade helper
    private DbHelper mDbHelper;
    private static DbProvider mDbProvider = null;

    public synchronized static DbProvider getInstance(Context context) {
        if (mDbProvider == null) {
            mDbProvider = new DbProvider(context);
        }
        return mDbProvider;
    }

    public DbProvider(Context context) {
        mContext = context;
        mDbHelper = new DbHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public DbProvider open() throws SQLException {
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDb.close();
    }

    private static final String DATABASE_TEST_SCENE_TABLE_CREATE = "create table " + SCENEINFO_TABLE + " (" +
            "_id integer primary key autoincrement, " +
            SCENEINFO_SCENE_ID_COLUMN + " integer, " +
            SCENEINFO_SCENE_NAME_COLUMN + " text" +
            ")";

    private static final String DATABASE_SUBJECT_TABLE_CREATE = "create table " + SUBJECTINFO_TABLE + " (" +
            "_id integer primary key autoincrement, " +
            SUBJECTINFO_SCENE_ID_COLUMN + " integer, " +
            SUBJECTINFO_SCENE_NAME_COLUMN + " text" +
            ")";

    private static final String DATABASE_EARHANG_TABLE_CREATE = "create table " + EARHANGINFO_TABLE + " (" +
            "_id integer primary key autoincrement, " +
            EARHANGINFO_SCENE_ID_COLUMN+ " integer, " +
            EARHANGINFO_SCENE_NAME_COLUMN + " text" +
            ")";

    private static final String DATABASE_SKIN_TABLE_CREATE = "create table " + SKININFO_TABLE + " (" +
            "_id integer primary key autoincrement, " +
            SKININFO_SCENE_ID_COLUMN + " integer, " +
            SKININFO_SCENE_NAME_COLUMN + " text" +
            ")";

    private static final String DATABASE_DEVICE_INFO_TABLE_CREATE = "create table " + DEVICENFO_TABLE + " (" +
            "_id integer primary key autoincrement, " +
            DEVICENFO_OWNER + " text, " +
            DEVICENFO_DEVICE_NAME + " text" +
            ")";

    //scene
    public long addSceneInfo(CfgDetailInfo info ) {
        long ret = -1;
        if (null != info) {
            ContentValues contentValues = new ContentValues();
            contentValues.put(SCENEINFO_SCENE_ID_COLUMN, info.getId());
            contentValues.put(SCENEINFO_SCENE_NAME_COLUMN, info.getDesc());
            Cursor cursor = mDb.query(SCENEINFO_TABLE, new String[]{SCENEINFO_SCENE_ID_COLUMN},
                    SCENEINFO_SCENE_ID_COLUMN + "=?", new String[]{Integer.toString(info.getId())},
                    null, null, null);
            if (cursor.getCount() > 0) {
                ret =  mDb.update(SCENEINFO_TABLE, contentValues,
                        SCENEINFO_SCENE_ID_COLUMN + "=?",
                        new String[]{Integer.toString(info.getId())});
            } else {
                ret = mDb.insert(SCENEINFO_TABLE, null, contentValues);
            }
            cursor.close();
        }
        return ret;
    }

    public int getSceneID(String sceneName) {
        int ret = -1;
        Cursor cursor = mDb.query(SCENEINFO_TABLE, new String[]{SCENEINFO_SCENE_ID_COLUMN},
                SCENEINFO_SCENE_NAME_COLUMN + "=?", new String[]{sceneName},
                null, null, null);
        if (cursor.moveToNext()) {
            ret =  cursor.getInt(cursor.getColumnIndex(SCENEINFO_SCENE_ID_COLUMN));
        }
        cursor.close();
        return ret;
    }

    public Cursor getAllSceneInfo() {
        return mDb.query(SCENEINFO_TABLE, null, null, null, null, null, null);
    }

    public boolean removeSceneInfo(int SceneID) {
        return mDb.delete(SCENEINFO_TABLE, SCENEINFO_SCENE_ID_COLUMN + "=?" + SceneID, null) > 0;
    }

    public boolean removeSceneInfo(String SceneName) {
        return mDb.delete(SCENEINFO_TABLE, SCENEINFO_SCENE_NAME_COLUMN + "=?" + SceneName, null) > 0;
    }

    public boolean removeAllSceneInfo() {
        return mDb.delete(SCENEINFO_TABLE, null, null) > 0;
    }

    //earhang
    public long addEarHangInfo(EarHangInfo info ) {
        long ret = -1;
        if (null != info) {
            ContentValues contentValues = new ContentValues();
            contentValues.put(EARHANGINFO_SCENE_ID_COLUMN, info.getId());
            contentValues.put(EARHANGINFO_SCENE_NAME_COLUMN, info.getName());
            Cursor cursor = mDb.query(EARHANGINFO_TABLE, new String[]{EARHANGINFO_SCENE_ID_COLUMN},
                    EARHANGINFO_SCENE_ID_COLUMN + "=?", new String[]{Integer.toString(info.getId())},
                    null, null, null);
            if (cursor.getCount() > 0) {
                ret =  mDb.update(EARHANGINFO_TABLE, contentValues,
                        EARHANGINFO_SCENE_ID_COLUMN+ "=?",
                        new String[]{Integer.toString(info.getId())});
            } else {
                ret = mDb.insert(EARHANGINFO_TABLE, null, contentValues);
            }
            cursor.close();
        }
        return ret;
    }

    public int getEarHangID(String earHangName) {
        int ret = -1;
        Cursor cursor = mDb.query(EARHANGINFO_TABLE, new String[]{EARHANGINFO_SCENE_ID_COLUMN},
                EARHANGINFO_SCENE_NAME_COLUMN + "=?", new String[]{earHangName},
                null, null, null);
        if (cursor.moveToNext()) {
            ret =  cursor.getInt(cursor.getColumnIndex(EARHANGINFO_SCENE_ID_COLUMN));
        }
        cursor.close();
        return ret;
    }

    public Cursor getAllEarHangInfo() {
        return mDb.query(EARHANGINFO_TABLE, null, null, null, null, null, null);
    }

    public boolean removeEarHangInfo(int EarHangID) {
        return mDb.delete(EARHANGINFO_TABLE, EARHANGINFO_SCENE_ID_COLUMN + "=?" + EarHangID, null) > 0;
    }

    public boolean removeEarHangInfo(String EarHangName) {
        return mDb.delete(EARHANGINFO_TABLE, EARHANGINFO_SCENE_ID_COLUMN + "=?" + EarHangName, null) > 0;
    }

    public boolean removeAllEarHangInfo() {
        return mDb.delete(EARHANGINFO_TABLE, null, null) > 0;
    }


    //subject
    public long addSubjectInfo(CfgDetailInfo info ) {
        long ret = -1;
        if (null != info) {
            ContentValues contentValues = new ContentValues();
            contentValues.put(SUBJECTINFO_SCENE_ID_COLUMN, info.getId());
            contentValues.put(SUBJECTINFO_SCENE_NAME_COLUMN, info.getDesc());
            Cursor cursor = mDb.query(SUBJECTINFO_TABLE, new String[]{SUBJECTINFO_SCENE_ID_COLUMN},
                    SUBJECTINFO_SCENE_ID_COLUMN + "=?", new String[]{Integer.toString(info.getId())},
                    null, null, null);
            if (cursor.getCount() > 0) {
                ret =  mDb.update(SUBJECTINFO_TABLE, contentValues,
                        SUBJECTINFO_SCENE_ID_COLUMN + "=?",
                        new String[]{Integer.toString(info.getId())});
            } else {
                ret = mDb.insert(SUBJECTINFO_TABLE, null, contentValues);
            }
            cursor.close();
        }
        return ret;
    }

    public int getSubjectID(String subjectName) {
        int ret = -1;
        Cursor cursor = mDb.query(SUBJECTINFO_TABLE, new String[]{SUBJECTINFO_SCENE_ID_COLUMN},
                SUBJECTINFO_SCENE_NAME_COLUMN + "=?", new String[]{subjectName},
                null, null, null);
        if (cursor.moveToNext()) {
            ret =  cursor.getInt(cursor.getColumnIndex(SUBJECTINFO_SCENE_ID_COLUMN));
        }
        cursor.close();
        return ret;
    }

    public Cursor getAllSubjectInfo() {
        return mDb.query(SUBJECTINFO_TABLE, null, null, null, null, null, null);
    }

    public boolean removeSubjectInfo(int SubjecctID) {
        return mDb.delete(SUBJECTINFO_TABLE, SUBJECTINFO_SCENE_ID_COLUMN + "=?" + SubjecctID, null) > 0;
    }

    public boolean removeSubjectInfo(String SubjectName) {
        return mDb.delete(SUBJECTINFO_TABLE, SUBJECTINFO_SCENE_NAME_COLUMN + "=?" + SubjectName, null) > 0;
    }

    public boolean removeAllSubjectInfo() {
        return mDb.delete(SUBJECTINFO_TABLE, null, null) > 0;
    }

    //skin
    public long addSkinInfo(CfgDetailInfo info ) {
        long ret = -1;
        if (null != info) {
            ContentValues contentValues = new ContentValues();
            contentValues.put(SKININFO_SCENE_ID_COLUMN, info.getId());
            contentValues.put(SKININFO_SCENE_NAME_COLUMN, info.getDesc());
            Cursor cursor = mDb.query(SKININFO_TABLE, new String[]{SKININFO_SCENE_ID_COLUMN},
                    SKININFO_SCENE_ID_COLUMN + "=?", new String[]{Integer.toString(info.getId())},
                    null, null, null);
            if (cursor.getCount() > 0) {
                ret =  mDb.update(SKININFO_TABLE, contentValues,
                        SKININFO_SCENE_ID_COLUMN + "=?",
                        new String[]{Integer.toString(info.getId())});
            } else {
                ret = mDb.insert(SKININFO_TABLE, null, contentValues);
            }
            cursor.close();
        }
        return ret;
    }

    public int getSkinID(String skinName) {
        int ret = -1;
        Cursor cursor = mDb.query(SKININFO_TABLE, new String[]{SKININFO_SCENE_ID_COLUMN},
                SKININFO_SCENE_NAME_COLUMN + "=?", new String[]{skinName},
                null, null, null);
        if (cursor.moveToNext()) {
            ret =  cursor.getInt(cursor.getColumnIndex(SKININFO_SCENE_ID_COLUMN));
        }
        cursor.close();
        return ret;
    }

    public Cursor getAllSkinInfo() {
        return mDb.query(SKININFO_TABLE, null, null, null, null, null, null);
    }

    public boolean removeSkinInfo(int skinID) {
        return mDb.delete(SKININFO_TABLE, SKININFO_SCENE_ID_COLUMN + "=?" + skinID, null) > 0;
    }

    public boolean removeSkinInfo(String skinName) {
        return mDb.delete(SKININFO_TABLE, SKININFO_SCENE_NAME_COLUMN + "=?" + skinName, null) > 0;
    }

    public boolean removeAllSkinInfo() {
        return mDb.delete(SKININFO_TABLE, null, null) > 0;
    }

    //device
    public long addDeviceInfo(DeviceInfo info ) {
        long ret = -1;
        if (null != info) {
            ContentValues contentValues = new ContentValues();
            contentValues.put(DEVICENFO_OWNER, info.getOwner());
            contentValues.put(DEVICENFO_DEVICE_NAME, info.getDeviceName());
            Cursor cursor = mDb.query(DEVICENFO_TABLE, null,
                    DEVICENFO_OWNER + "=? and " + DEVICENFO_DEVICE_NAME + "=?", new String[]{info.getOwner(), info.getDeviceName()},
                    null, null, null);
            if (cursor.getCount() <= 0) {
                ret =  mDb.insert(DEVICENFO_TABLE, null, contentValues);
            }
            cursor.close();
        }
        return -1;
    }

    public Cursor getAllDeviceInfo() {
        return mDb.query(DEVICENFO_TABLE, null, null, null, null, null, null);
    }

    public boolean removeDeviceInfo(String owner, String name) {
        if (owner != null && name != null) {
            return mDb.delete(DEVICENFO_TABLE, DEVICENFO_OWNER + "=? and " + DEVICENFO_DEVICE_NAME + "=?", new String[]{owner, name}) > 0;
        } else if (owner != null && name == null) {
            return mDb.delete(DEVICENFO_TABLE, DEVICENFO_OWNER + "=?", new String[]{owner}) > 0;
        } else if (owner == null && name != null) {
            return mDb.delete(DEVICENFO_TABLE, DEVICENFO_DEVICE_NAME + "=?", new String[]{name}) > 0;
        }
        return false;
    }

    public boolean removeAllDeviceInfo() {
        return mDb.delete(DEVICENFO_TABLE, null, null) > 0;
    }



    private static class DbHelper extends SQLiteOpenHelper {
        public DbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        // Called when no database exists in disk and the helper class needs
        // to create a new one.
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_TEST_SCENE_TABLE_CREATE);
            db.execSQL(DATABASE_EARHANG_TABLE_CREATE);
            db.execSQL(DATABASE_DEVICE_INFO_TABLE_CREATE);
            db.execSQL(DATABASE_SUBJECT_TABLE_CREATE);
            db.execSQL(DATABASE_SKIN_TABLE_CREATE);

        }

        // Called when there is a database version mismatch meaning that the version
        // of the database on disk needs to be upgraded to the current version.
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // Upgrade the existing database to conform to the new version. Multiple
            // previous versions can be handled by comparing _oldVersion and _newVersion
            // values.

            // The simplest case is to drop the old table and create a new one.
            db.execSQL("DROP TABLE IF EXISTS " + SCENEINFO_TABLE + ";");
            db.execSQL("DROP TABLE IF EXISTS " + DEVICENFO_TABLE + ";");
            db.execSQL("DROP TABLE IF EXISTS " + EARHANGINFO_TABLE + ";");
            db.execSQL("DROP TABLE IF EXISTS " + SUBJECTINFO_TABLE + ";");
            db.execSQL("DROP TABLE IF EXISTS " + SKININFO_TABLE + ";");
            // Create a new one.
            onCreate(db);
        }
    }
}
